/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package VistaConexion;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class Conexion {
    

    private static java.sql.Connection conectar;
    private Usuario user;
    String db;
    
    public Conexion (Usuario user) throws SQLException{
        this.user = user;
        conectar = DriverManager.getConnection(user.getUrl(), user.getUser() , user.getPassword());
        this.db = "";
    } 
    
    public ArrayList getDatabases(){
        
        ArrayList listaBD = new ArrayList(); 
        
        try{
            Class.forName(user.getDriver());
            
            Statement createStatement = conectar.createStatement();
            ResultSet resultSet;
            resultSet = createStatement.executeQuery(user.getQuery());
            
            while (resultSet.next()) {
                String nombre = resultSet.getString(1);                         
                listaBD.add(nombre);
            }
            
            createStatement.close();
            conectar.close();
            
        }
        catch(SQLException ex){
            System.out.println("Problema en usuario: " + ex.toString());
        }
        catch(ClassNotFoundException ex){
            System.out.println("Problema en usuario: " + ex.toString());
        }finally{
            return listaBD;
        }
    }
    
   
    public ArrayList getTables() throws ClassNotFoundException{
        
        ArrayList listaTable = new ArrayList();
        
        try{
            Class.forName( user.getDriver() );
            
            java.sql.Connection conTmp = DriverManager.getConnection(user.getUrl() + db, user.getUser() , user.getPassword());
            
            Statement createStatement = conTmp.createStatement();
            
            ResultSet resultSet;
            resultSet = createStatement.executeQuery( user.getQueryTables() );
            
            while (resultSet.next()) {
                String nomTabla = resultSet.getString(1);
                if ("org.postgresql.Driver".equals(user.getDriver())) nomTabla = resultSet.getString(2);
                listaTable.add( nomTabla );
            }
            
            createStatement.close();
            conTmp.close();
        }
        catch(SQLException ex){
            System.out.println("Problema en clase usuario (loadTables) 1: " + ex.toString());
        }
        catch(ClassNotFoundException ex){
            System.out.println("Problema en clase usuario (loadTables) 2: " + ex.toString());
        }
        finally{
            return listaTable;
        }
    }
    
    public ArrayList getFields(String table){
        
        ArrayList listaFields = new ArrayList();
        
        try{
            Class.forName( user.getDriver() );
            
            java.sql.Connection conTmp = DriverManager.getConnection(user.getUrl() + db, user.getUser() , user.getPassword());
            
            Statement createStatement = conTmp.createStatement();
            
            ResultSet resultSet;
            String consulta = "";
            
            if ( "org.postgresql.Driver".equals(user.getDriver()) ) consulta = user.getQueryNameFields() + "'" + table + "';";
            else consulta = user.getQueryNameFields() + table;
            
            resultSet = createStatement.executeQuery( consulta );
            
            while (resultSet.next()) {
                String nomField = resultSet.getString(1);
                listaFields.add( nomField );
            }
            
            createStatement.close();
            conTmp.close();
        }
        catch(SQLException ex){
            System.out.println("Problema en clase usuario (loadField) 1: " + ex.toString());
        }
        catch(ClassNotFoundException ex){
            System.out.println("Problema en clase usuario (loadField) 2: " + ex.toString());
        }
        finally{
            return listaFields;
        }
    }
    
    public String getConexion() {
        return conectar.toString();
    }
    public Usuario getUser(){ return this.user;}
    
    public void setDB(String db){ this.db = db; }
    public String getDB(){ return this.db; }
}
